using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.Data.SqlClient;
using Smo =Microsoft.SqlServer.Management.Smo;
using System.Diagnostics;

namespace SQLScripting
{
    public partial class frmScripting : Form
    {
        public frmScripting()
        {
            InitializeComponent();
        }

        //Local variables to hold the connection and the command objects
        SqlConnection _connection;
        Smo.Server _smoServer;
        SqlCommand _listenCommand;
        SqlCommand _respondCommand;
        
        /// <summary>
        /// Load event of the form where we initialis everything
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {

            //create a new connection and set the connection string and open it
            _connection = new SqlConnection ("Data source=.\\sql2005;integrated security=True");
            _connection.Open ();
 
            //create a link to the server in smo
            _smoServer = new Smo.Server(".\\sql2005");

            //create the command to listen for scripting requests
            _listenCommand = new SqlCommand ();
            _listenCommand.CommandType = CommandType.StoredProcedure ; 
            _listenCommand.CommandText = "msdb.dbo.up_SqlBits_Scripter_ReceiveScriptRequest";
            _listenCommand.Connection = _connection ;

            //create the command to send the script for the object
            _respondCommand = new SqlCommand ();
            _respondCommand.CommandType = CommandType.StoredProcedure ;
            _respondCommand.CommandText = "msdb.dbo.up_SqlBits_Scripter_ReturnScriptRequest";
            _respondCommand.Parameters.Add ("@dialog",SqlDbType.UniqueIdentifier );
            _respondCommand.Parameters.Add ("@script",SqlDbType.NText );
            _respondCommand.Connection = _connection ;

            #region Background Thread Code
            _timer = new BackgroundWorker();
            _timer.DoWork +=new DoWorkEventHandler(timer_DoWork);
            _timer.RunWorkerAsync();
            _timer.WorkerReportsProgress = true;
            _timer.ProgressChanged += new ProgressChangedEventHandler(_timer_ProgressChanged);
            #endregion
        }

        /// <summary>
        /// This is the function that waits for a request from the user and then generates the
        /// script for object
        /// </summary>
        /// <returns></returns>
        private bool CheckForMessages()
        {
            //Start the timer
            Stopwatch timer = new Stopwatch();
            Guid _dialog = Guid.Empty;
            string _objectName="";

            try
            {
                //Listen for requests
                SqlDataReader request = _listenCommand.ExecuteReader();

                //read the request
                if (request.Read())
                {
                    timer.Start();

                    //we have a request
                    //get the parameters
                    string _database = request.GetString(0);
                    _objectName = request.GetString(1);
                    _dialog = request.GetGuid(2);
                    
                    //We've finished with the request reader so we can close it
                    request.Close();

                    //build the scripting obtions 
                    System.Collections.Specialized.StringCollection _scriptElements;

                    Smo.ScriptingOptions _opt = new Smo.ScriptingOptions();
                    _opt.ClusteredIndexes = true;
                    _opt.IncludeIfNotExists = true;
                    _opt.NoCollation = true;
                    _opt.XmlIndexes = true;
                    _opt.DriAll = true;
                    _opt.FullTextCatalogs = true;
                    _opt.FullTextIndexes = true;
                    _opt.ExtendedProperties = true;
                    _opt.Permissions = true;
                    
                    //Script the object
                    Smo.Database scriptingDb = _smoServer.Databases[_database];
                    if (scriptingDb.Tables.Contains(_objectName))
                    {
                        _scriptElements = scriptingDb.Tables[_objectName].Script(_opt);

                        //convert the script elements into a string to return to the client
                        StringBuilder sb = new StringBuilder();
                        sb.AppendLine("--(c) 2007 Simon Sabin : SQL Server MVP : http://sqlblogcasts.com/blogs/simons ");
                        foreach (string item in _scriptElements)
                        {
                            sb.AppendLine(item);
                            sb.AppendLine("GO");
                        }

                        //Convert the strib builder to a string
                        string script = sb.ToString();

                        SendResponse(_dialog, script);

                    }
                    else
                    {
                        SendResponse(_dialog, "object does not exists or isn't a table");

                    }
                        //Record the time taken
                        timer.Stop();
                    _timer.ReportProgress(0, string.Format("{0}~{1}~", _objectName, timer.ElapsedMilliseconds));
                }
                else
                {
                    //No record found so close the reader
                    request.Close();
                }
                //Return success
                return true;
            }
            catch (Exception ex)
            {
                string sMessage = string.Format("{0}:{1}:{2}", _objectName,timer.ElapsedMilliseconds, ex.Message);

                //Send the error to the requestor
                if (_dialog != Guid.Empty)
                    SendResponse(_dialog, sMessage);

                //Set the status bar with the error
                SetStatusBar(sMessage );
                return false;
            }
        }

        private  void SendResponse(Guid _dialog, string script)
        {
            //Send the script back to the requestor
            _respondCommand.Parameters["@dialog"].Value = _dialog;
            _respondCommand.Parameters["@script"].Value = script;
            _respondCommand.ExecuteNonQuery();
        }

        #region Background thread methods
        /// <summary>
        /// Method used by the background worker thread
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void timer_DoWork(object sender, DoWorkEventArgs e)
        {
            
            try
            {
                //Check for requests
                while (CheckForMessages())
                {
                }

            }
            catch (Exception ex)
            {
                //An error has occurred so put the error on the status bar
                SetStatusBar (string.Format("~~Processing stopped {0}", ex.Message));

            }
        }

        /// <summary>
        /// Allows the background thread to update the UI
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void _timer_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            //Add the information passed to the List view
            switch (e.ProgressPercentage)
            {
                case 0:

                    lstRequest.Items.Add(new ListViewItem(e.UserState.ToString().Split('~')));
                    tlstrpStatus.Text = "";

                    break;
                case 1:
                    tlstrpStatus.Text = e.UserState.ToString();
                    break;
            }
            
        }

        /// <summary>
        /// Wrapper function to set the status of the status bar
        /// </summary>
        /// <param name="status"></param>
        void SetStatusBar(string status)
        {
            _timer.ReportProgress(1, status);
        }
        #endregion
    }
}